using System.Data;
using System.Data.SqlClient;
using NetMigration.Exceptions;

namespace NetMigration.DbProviders
{
    public class SQLServerProvider : IDbProvider
    {
        private const string ERROR_MESSAGE = "Occured an error when executing query";
        private readonly string connectionString;

        public SQLServerProvider()
        {
        }

        public SQLServerProvider(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void ExecuteCommand(string creationText)
        {
            SqlConnection con = GetConnection();
            var cmd = new SqlCommand(creationText, con);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw new DbProviderException(ERROR_MESSAGE, e);
            }
            finally
            {
                con.Close();
            }
        }

        public object ExecuteScala(string commandText)
        {
            SqlConnection connection = GetConnection();
            var cmd = new SqlCommand(commandText, connection);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                throw new DbProviderException(ERROR_MESSAGE, e);
            }
            finally
            {
                connection.Close();
            }
        }

        private SqlConnection GetConnection()
        {
            var con =
                new SqlConnection(connectionString);
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            return con;
        }
    }
}